/***********************************************************************************************************************************
 * Name:	DB_Files.sql
 * Author:	Frank Figearo (frank@sqlnerd.me)
 * Summary:	Show physical locations of all database files on the server.
 */
SET IMPLICIT_TRANSACTIONS OFF;
BEGIN TRY DROP TABLE #DBFiles END TRY BEGIN CATCH END CATCH;
CREATE TABLE #DBFiles (
	[server]		SYSNAME,
	[database]		SYSNAME,
	[name]			SYSNAME,
	[physical_name]	NVARCHAR(260),
	[type_desc]		NVARCHAR(60),
	[state_desc]	NVARCHAR(60));
DECLARE
  @tsql_template	NVARCHAR(MAX),
  @tsql_command		NVARCHAR(MAX);
SET @tsql_template= N'INSERT INTO #DBFiles SELECT @@SERVERNAME,  ''?'', [name], [physical_name], [type_desc], [state_desc] FROM [?].sys.database_files WHERE type IN (0,1)';

DECLARE list CURSOR LOCAL FAST_FORWARD FOR SELECT
	tsql_command= REPLACE(@tsql_template, '?', name)
  FROM master.sys.databases WHERE [state] = 0;
OPEN list;
WHILE 1=1 BEGIN;
  FETCH NEXT FROM list INTO @tsql_command;
  IF @@FETCH_STATUS <> 0 BREAK;
  EXECUTE (@tsql_command);
END;
CLOSE list;
DEALLOCATE list;

SELECT * FROM #DBFiles ORDER BY type_desc, physical_name;
GO